﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using DBUtility;

namespace OracleDAL.DutyAssignment
{
    public class D_DutyDetail
    {
        /// <summary>
		/// 得到一个对象实体
		/// </summary>
		public Model.DutyAssignment.M_DutyDetail GetModel(string ID)
		{
            string strSql = "select * from duty_arrange_detail where id='" + ID + "'";

            Model.DutyAssignment.M_DutyDetail model = new Model.DutyAssignment.M_DutyDetail();
            DataSet ds = OracleHelper.Query(strSql);
			if(ds.Tables[0].Rows.Count>0)
			{
				model.ID=ds.Tables[0].Rows[0]["ID"].ToString();
				model.DUTYID=ds.Tables[0].Rows[0]["DUTYID"].ToString();
				model.USERISN=ds.Tables[0].Rows[0]["USERISN"].ToString();
				model.DUTYNAME=ds.Tables[0].Rows[0]["DUTYNAME"].ToString();
				if(ds.Tables[0].Rows[0]["STARTTIME"].ToString()!="")
				{
					model.STARTTIME=DateTime.Parse(ds.Tables[0].Rows[0]["STARTTIME"].ToString());
				}
				if(ds.Tables[0].Rows[0]["ENDTIME"].ToString()!="")
				{
					model.ENDTIME=DateTime.Parse(ds.Tables[0].Rows[0]["ENDTIME"].ToString());
				}
				model.DUTYTYPE=ds.Tables[0].Rows[0]["DUTYTYPE"].ToString();
				model.DUTYREMARK=ds.Tables[0].Rows[0]["DUTYREMARK"].ToString();
				model.REMARK=ds.Tables[0].Rows[0]["REMARK"].ToString();
				return model;
			}
			else
			{
				return null;
			}
		}

        /// <summary>
		///  增加一条数据
		/// </summary>
		public bool Add(Model.DutyAssignment.M_DutyDetail model)
		{
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO DUTY_ARRANGE_DETAIL(ID,DUTYID,USERISN,DUTYNAME,STARTTIME,ENDTIME,DUTYTYPE,DUTYREMARK,REMARK) ");
            strSql.Append("VALUES(:ID,:DUTYID,:USERISN,:DUTYNAME,:STARTTIME,:ENDTIME,:DUTYTYPE,:DUTYREMARK,:REMARK)");
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.VarChar,50),
					new OracleParameter(":DUTYID", OracleType.VarChar,50),
					new OracleParameter(":USERISN", OracleType.VarChar,50),
					new OracleParameter(":DUTYNAME", OracleType.VarChar,100),
					new OracleParameter(":STARTTIME", OracleType.DateTime),
					new OracleParameter(":ENDTIME", OracleType.DateTime),
					new OracleParameter(":DUTYTYPE", OracleType.VarChar,10),
					new OracleParameter(":DUTYREMARK", OracleType.VarChar,50),
					new OracleParameter(":REMARK", OracleType.VarChar,4000)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.DUTYID;
            parameters[2].Value = model.USERISN;
            parameters[3].Value = model.DUTYNAME;
            parameters[4].Value = model.STARTTIME;
            parameters[5].Value = model.ENDTIME;
            parameters[6].Value = model.DUTYTYPE;
            parameters[7].Value = model.DUTYREMARK;
            parameters[8].Value = model.REMARK;

            return OracleHelper.ExecuteSql(strSql.ToString(), parameters) > 0 ? true : false;
		}

        
		/// <summary>
		///  更新一条数据
		/// </summary>
        public bool Update(Model.DutyAssignment.M_DutyDetail model)
		{
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE DUTY_ARRANGE_DETAIL SET DUTYID=:DUTYID,USERISN=:USERISN,");
            strSql.Append("DUTYNAME=:DUTYNAME,STARTTIME=:STARTTIME,ENDTIME=:ENDTIME,DUTYTYPE=:DUTYTYPE,");              strSql.Append("DUTYREMARK=:DUTYREMARK,REMARK=:REMARK  where ID=:ID");
            
			OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.VarChar,50),
					new OracleParameter(":DUTYID", OracleType.VarChar,50),
					new OracleParameter(":USERISN", OracleType.VarChar,50),
					new OracleParameter(":DUTYNAME", OracleType.VarChar,100),
					new OracleParameter(":STARTTIME", OracleType.DateTime),
					new OracleParameter(":ENDTIME", OracleType.DateTime),
					new OracleParameter(":DUTYTYPE", OracleType.VarChar,10),
					new OracleParameter(":DUTYREMARK", OracleType.VarChar,50),
					new OracleParameter(":REMARK", OracleType.VarChar,4000)};
			parameters[0].Value = model.ID;
			parameters[1].Value = model.DUTYID;
			parameters[2].Value = model.USERISN;
			parameters[3].Value = model.DUTYNAME;
			parameters[4].Value = model.STARTTIME;
			parameters[5].Value = model.ENDTIME;
			parameters[6].Value = model.DUTYTYPE;
			parameters[7].Value = model.DUTYREMARK;
			parameters[8].Value = model.REMARK;

            return OracleHelper.ExecuteSql(strSql.ToString(), parameters) > 0 ? true : false;
		}

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM DUTY_ARRANGE_DETAIL  ");
            if (strWhere != "")
            {
                strSql.Append(" WHERE " + strWhere);
            }

            return OracleHelper.ExecuteSql(strSql.ToString()) > 0 ? true : false;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,DUTYID,USERISN,DUTYNAME,STARTTIME,ENDTIME,DUTYTYPE,DUTYREMARK,REMARK ");
            strSql.Append(" FROM DUTY_ARRANGE_DETAIL ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }

            strSql.Append(" order by starttime asc ");
            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 得到统计结果
        /// </summary>
        /// <param name="unitisn"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public DataTable GetStatisticTable(string unitisn,string startTime, string endTime)
        {
            DataTable rtDT = new DataTable();
            DataColumn dc1 = new DataColumn("name", typeof(string));
            DataColumn dc2 = new DataColumn("dutyAll", typeof(int));
            DataColumn dc3 = new DataColumn("dutyZhoumo", typeof(int));
            DataColumn dc4 = new DataColumn("dutyJieri", typeof(int));
            DataColumn dc5 = new DataColumn("dutyNormal", typeof(int));

            rtDT.Columns.Add(dc1);
            rtDT.Columns.Add(dc2);
            rtDT.Columns.Add(dc5);
            rtDT.Columns.Add(dc3);
            rtDT.Columns.Add(dc4);
            

            DataRow dr;

            string sqlPerson = "select distinct p.userisn from duty_arrange_person p,duty_arrange_new n where n.unitisn='" + unitisn + "' and n.id=p.dutyid";
            DataTable dtPerson = OracleHelper.Query(sqlPerson).Tables[0];
            if (dtPerson.Rows.Count > 0)
            {
                //  string sqlDetail = "select * from duty_arrange_detail where starttime >= to_date('" + startTime + "','yyyy-mm-dd hh24:mi:ss') and starttime <= to_date('" + endTime + "','yyyy-mm-dd hh24:mi:ss')";
                string sqlDetail = "select * from duty_arrange_detail ";
                DataTable dtDetail = OracleHelper.Query(sqlDetail).Tables[0];
                for (int i = 0; i < dtPerson.Rows.Count; i++)
                {
                    dr = rtDT.NewRow();
                    DataRow[] rowAll = dtDetail.Select("userisn='" + dtPerson.Rows[i]["userisn"].ToString() + "'");
                    DataRow[] rowZhoumo = dtDetail.Select("userisn='" + dtPerson.Rows[i]["userisn"].ToString() + "' and dutyremark = '周末班'");
                    DataRow[] rowJieri = dtDetail.Select("userisn='" + dtPerson.Rows[i]["userisn"].ToString() + "' and dutyremark not in('平常班','周末班')");
                    DataRow[] rowNormal = dtDetail.Select("userisn='" + dtPerson.Rows[i]["userisn"].ToString() + "' and dutyremark = '平常班'");
                    dr["name"] = new OracleDAL.Sec.User().GetModel(dtPerson.Rows[i]["userisn"].ToString()).UserName;
                    dr["dutyAll"] = rowAll.Length;
                    dr["dutyZhoumo"] = rowZhoumo.Length;
                    dr["dutyJieri"] = rowJieri.Length;
                    dr["dutyNormal"] = rowNormal.Length;

                    rtDT.Rows.Add(dr);
                }
            }

            return rtDT;
        }
    }
}
